import openpyxl # pip3 install openpyxl
from openpyxl.styles import Font, Alignment


# 封装读excel方法，需要提供表头（要读的列的标题列表）
# 返回一个list，其中每一行是个map，key为列标题，value为单元格内容
def sheetReader(sheet, columnTitles:list) -> list:
    #保存列标题对应的序号
    columnIndex = {}
    
    # 查表里column_titles中每一项的列编号
    for i in range(1, sheet.max_column + 1):
        for title in columnTitles:
            if title == sheet.cell(1, i).value.strip():
                columnIndex[title] = i
    # 检查excel表里是否包含了col_info中的所有列
    for title in columnIndex:
        if title not in columnTitles:
            print('excel column title error: lost', title)
            raise Exception

    # 按行读取excel表
    data = []
    for row in sheet.iter_rows(min_row=2):
        line = {}
        for title in columnTitles:
            val = row[columnIndex[title] - 1].value
            line[title] = val
        data.append(line)

    return data


# 封装写excel方法
class sheetWriter:

    def __init__(self, sheet) -> None:
        self.cur_sheet = sheet
        self.cur_row = 1
        self.cur_col = 1

    # 设置所有列的宽度（字符数）
    def setColumnsWidth(self, col_width_list:list) -> None:
        c = ord('A')
        for w in col_width_list[:26]:
            self.cur_sheet.column_dimensions[chr(c)].width = w
            c += 1

    # 跳到第row行，跳转后位于第1列
    def gotoRow(self, row:int) -> None:
        self.cur_row = row
        self.cur_col = 1

    # 向下跳n行，跳转后位于第1列
    def nextRow(self, n:int = 1) -> None:
        self.cur_row += n
        self.cur_col = 1

    # 向当前行的第col列写入内容
    def setCellValue(self, col, val, center=False, wrap=False, bold=False, number_format=None) -> None:
        cell = self.cur_sheet.cell(self.cur_row, col)
        cell.value = val
        if center:
            cell.alignment = Alignment(horizontal='center', vertical='center', wrapText=wrap)
        else:
            cell.alignment = Alignment(vertical='center', wrapText=wrap)
        if bold:
            cell.font = Font(bold=True)
        if isinstance(val, float):
            if number_format:
                cell.number_format = number_format
            else:
                cell.number_format = '0.0'
    
    # 向当前单元格写入内容，然后跳到当前行的下一个单元格
    # val为空时直接跳转
    def fillCellThenMove(self, val=None, center=False, wrap=False, bold=False, number_format=None) -> None:
        if val:
            self.setCellValue(self.cur_col, val, center, wrap, bold, number_format)
        self.cur_col += 1
        

if __name__ == '__main__':
    
    # demo for sheetReader

    wb = openpyxl.load_workbook('demo.xlsx')
    sheet = wb['Sheet1']

    title_list = ['项目', '客户', '数量']
    lines = sheetReader(sheet, title_list)
    
    for title in title_list:
        print(title, end=', ')
    print()
    for line in lines:
        for title in line:
            print(line[title], end=', ')
        print()

    wb.close()


    # demo for sheetWriter

    wb = openpyxl.Workbook()

    sheet = wb.active
    sheet.title = 'sheet_1'
    s = sheetWriter(sheet)
    s.setColumnsWidth([32, 8, 8, 24])
    s.gotoRow(1)

    s.fillCellThenMove("这是第一行的第1格")
    s.fillCellThenMove(1001, center=True, bold=True)
    s.fillCellThenMove(100.01, number_format='0.00')
    s.fillCellThenMove('这是第4格')
    s.nextRow(2)
    s.fillCellThenMove("这是第三行")

    sheet = wb.create_sheet('sheet_2')
    s = sheetWriter(sheet)
    s.setColumnsWidth([32, 16, 12])
    s.gotoRow(1)

    s.fillCellThenMove('项目', center=True, bold=True)
    s.fillCellThenMove('客户', center=True, bold=True)
    s.fillCellThenMove('经理', center=True, bold=True)

    s.nextRow()
    s.fillCellThenMove('项目名称')
    s.fillCellThenMove('客户名称')
    s.fillCellThenMove('经理名称')

    wb.save('test.xlsx')
    wb.close()

    print()
    print('sheetWriter ok')